# -*- coding: utf-8 -*-
"""
Created on Thu Aug 17 17:04:18 2017

@author: ahchpr

E-mail: hellochenpro@hotmail.com
"""


import os
import xlwt
import xlrd
import datetime



'''打开一个工作excel 表格 ======================================
'''
def open_one_xls(file_name):
    try:
        data = xlrd.open_workbook(file_name)
        table = data.sheet_by_index(0)
        return (data,table)
    except Exception as e:
        print (str(e))
  
      
'''获取GM 排班计划 ============================================
'''        
def fetch_work_plan_data(wb, table):
    '''排班类型 集合'''
    work_plan_set = {'E', u'早', u'中', u'夜', u'休', u'假'}
    
    '''获取行、列数'''
    nrows = table.nrows
    ncols = table.ncols   
    
    '''获取日期'''
    date_temp = []
    for i in range(1, ncols):
       date_temp.append(xlrd.xldate.xldate_as_datetime(table.cell(0, i).value, 0))
    
    date_list = [item.strftime("%Y-%m-%d") for item in date_temp]
       
    '''获取名字 集合'''
    gm_name_set = set()
    for i in range(2, nrows):
        gm_name_set.add((table.cell(i, 0).value))
    gm_name_set.remove('')
   
    '''获取排班顺序'''
    gm_work_plan_dict = {}
    for row in range(2,nrows):
        name = table.cell(row, 0).value
        if name not in gm_name_set :
            continue
        if name not in gm_work_plan_dict :
            gm_work_plan_dict[name] = {}
        for col in  range(1, ncols):
            work = table.cell(row, col).value
            if work in work_plan_set:
                gm_work_plan_dict[name][date_list[col - 1]]=work
        
    return (gm_work_plan_dict)
    
    
'''获取GM 打卡记录 =============================================
'''
def fetch_record_data(wb, table):
    '''获取行数'''
    nrows = table.nrows
    ncols = table.ncols   
    
    '''GM 打卡记录'''
    gm_record_dict = {}
    
    for row in range(1, nrows):
        name = table.cell(row, 0).value
        if name  not in gm_record_dict :
            gm_record_dict[name] = {}
        for col in range(1, ncols):
            if col == 1 :
                record_day = table.cell(row, col).value
#                record_day = datetime.datetime.strptime(temp, "%Y-%m-%d")
#                print(record_day)
            elif col == 2 :
                temp_tag_card_list = table.cell(row, col).value.split(' ')
                if len(temp_tag_card_list) == 1 and temp_tag_card_list[0] == '':
                    '''空， 代表可能休假，请假或者 漏打卡了，在这里也做了记录'''
                    gm_record_dict[name][record_day]= temp_tag_card_list
                else:
                    record_time = [ "{} {}".format(record_day, item) for item in temp_tag_card_list ]
                    gm_record_dict[name][record_day] = record_time
                                  
    return  (gm_record_dict)
        
  

'''统计GM 每天的工作时长 ==========================================
'''
def static_work_duration_time(plan, record):
    
    work_time_dict = {u'早': ['09:00', '18:00'], u'中': ['13:30', '22:00'],
                      u'夜': ['22:00', '09:00'], 'E': ['10:30', '19:30']}
    
    '''最终结果存放在字典里'''
    gm_static_work_duration_dict = {}
    
    
    for gm_name, tag_card in record.items():
     
        if gm_name in plan:
            work_plan = plan[gm_name]
            
            '''对打卡记录的 字典按照key（日期） 排序调整顺序'''
            tag_card_sorted = []
            tag_card_sorted = sorted(tag_card.items(), key=lambda item: item[0])
#            print (gm_name, tag_card_sorted, "\n")
            
            '''对work_plan 进行排序'''
            work_plan_sorted = []
            work_plan_sorted = sorted(work_plan.items(), key = lambda item: item[0])
            
            '''计算工作时间'''
            for seq, item in enumerate(tag_card_sorted):
                '''日期'''
                record_day = item[0]
                morning_noon_night_E = work_plan[ record_day ]
                
                '''星期'''
                day_of_weed = datetime.datetime.strptime(record_day, "%Y-%m-%d").weekday()
                
                '''
                    对工作时长分  [u'早', u'中','E']， 和 [u'夜'] 两个大类型进行统计 =======================
                '''
                if gm_name not in gm_static_work_duration_dict:
                    gm_static_work_duration_dict[gm_name] = {}
                
                # 工作时长 ：单位秒
                delta_seconds = ''         
                # 打卡状态 ： 1.漏打卡
                tag_card_status = ''
                # 是否迟到
                tag_card_tardy = ''
                # 是否早退
                tag_card_early_leave = ''
                                
                if morning_noon_night_E in [u'早', u'中','E']:
                    '''是否漏打卡'''
                    if len(item[1]) <= 1:
#                        print(gm_name, record_day, u"漏打卡")
                        tag_card_status = u"异常"
                        delta_seconds = 0
                        tag_card_tardy = u'异常'
                        tag_card_early_leave = u'异常'
                        
                    else:
                        ''' 打卡两次的，才进行迟到早退统计 '''
                        tag_card_tardy, tag_card_early_leave = static_gm_tardy_or_early(morning_noon_night_E, item)
                        tag_card_status = u'正常'
                        '''开始打卡时间'''
                        start_tag_time =  item[1][0]
                        start_datetime = datetime.datetime.strptime(start_tag_time, "%Y-%m-%d %H:%M")
                        
                        '''结束打卡时间'''
                        end_tag_time = item[1][1]
                        end_datetime = datetime.datetime.strptime(end_tag_time, "%Y-%m-%d %H:%M")
                        
                        '''时间差'''
                        delta_seconds = (end_datetime - start_datetime).seconds
                                        
                    gm_static_work_duration_dict[gm_name][record_day] = [morning_noon_night_E, round(delta_seconds/3600,2), day_of_weed + 1, tag_card_status, tag_card_tardy, tag_card_early_leave]
                    
                elif morning_noon_night_E in [u'夜']:
                    tag_card_status, tag_card_tardy, tag_card_early_leave, delta_seconds = static_gm_night(tag_card_sorted, seq)
                    
#                    suppose_night_datetime = datetime.datetime.strptime(record_day+ " 21:00", "%Y-%m-%d %H:%M")                    
#                    if len(item[1]) == 2 :
#                        static_gm_night(tag_card_sorted, seq)
#                        start_datetime = datetime.datetime.strptime(item[1][1], "%Y-%m-%d %H:%M")
#                        if start_datetime > suppose_night_datetime:
#                            if seq + 1 >= len(tag_card_sorted) :
#                                tag_card_status = u'未统计'
#                                delta_seconds = 0
#                            else:
#                                end_time = tag_card_sorted[seq + 1][1][0]
#                                if end_time == '':
#                                    tag_card_status = u'异常'
#                                    delta_seconds = 0
#                                else:
#                                    end_datetime = datetime.datetime.strptime(end_time, "%Y-%m-%d %H:%M")
#                                    delta_seconds = (end_datetime - start_datetime).seconds
#                                    tag_card_status = u'正常'
#                    elif len(item[1]) == 1 :
#                        start_datetime = datetime.datetime.strptime(item[1][0], "%Y-%m-%d %H:%M")
#                        if start_datetime > suppose_night_datetime:
#                            if seq + 1 >= len(tag_card_sorted) :
#                                tag_card_status = u'未统计'
#                                delta_seconds = 0
#                            else:
#                                end_time = tag_card_sorted[seq + 1][1][0]
#                                if end_time == '':
#                                    tag_card_status = u'异常'
#                                    delta_seconds = 0
#                                else:
#                                    end_datetime = datetime.datetime.strptime(end_time, "%Y-%m-%d %H:%M")
#                                    tag_card_status = u'正常'
#                                    delta_seconds = (end_datetime - start_datetime).seconds
#                    elif item[1] == '':
#                        tag_card_status = u'异常'
#                        delta_seconds = 0
                    
                    gm_static_work_duration_dict[gm_name][record_day] = [morning_noon_night_E, round(delta_seconds/3600,2), day_of_weed + 1 , tag_card_status, tag_card_tardy, tag_card_early_leave]
                
                elif morning_noon_night_E in [u'休', u'假']:
                    work_status = morning_noon_night_E
                    gm_static_work_duration_dict[gm_name][record_day] = [work_status]*6
                    
    return (gm_static_work_duration_dict)
                       
                                        
'''统计 早，中班和E班就是销售组客服的迟到早退 ===========================================
'''
def  static_gm_tardy_or_early(morning_noon_night_E, tag_card_time):
    
    work_time_dict = {u'早': ['09:00', '18:00'], u'中': ['13:30', '22:00'],
                      u'夜': ['22:00', '09:00'], 'E': ['10:30', '19:30']}
    '''日期'''
    record_day = tag_card_time[0]
    
    '''星期'''
    day_of_weed = datetime.datetime.strptime(record_day, "%Y-%m-%d").weekday()
    
    '''确定标准的上下班时间'''
    stander_start_time      = ''
    stander_end_time        = ''
    if morning_noon_night_E == 'E' and day_of_weed == 5:
        stander_start_time = record_day + ' ' + work_time_dict['E'][0]
        stander_end_time = record_day + ' ' + '16:30'
               
    elif morning_noon_night_E == 'E' and day_of_weed != 5:
        stander_start_time = record_day + ' ' + work_time_dict['E'][0]
        stander_end_time = record_day + ' ' + work_time_dict['E'][1]
        
        
    elif morning_noon_night_E in [u'早', u'中'] :
        stander_start_time = record_day + ' ' + work_time_dict[morning_noon_night_E][0]
        stander_end_time = record_day + ' ' + work_time_dict[morning_noon_night_E][1]

    stander_start_datetime = datetime.datetime.strptime(stander_start_time, "%Y-%m-%d %H:%M")
    stander_end_datetime   = datetime.datetime.strptime(stander_end_time, "%Y-%m-%d %H:%M")
  
    ''' 计算是否迟到早退 '''
    # 是否迟到
    tag_card_tardy = u'正常'
    # 是否早退
    tag_card_early_leave = u'正常'
    '''只对打卡两次的记录进行统计'''
    if len(tag_card_time) == 2:
        if datetime.datetime.strptime(tag_card_time[1][0], "%Y-%m-%d %H:%M") >  stander_start_datetime :
            tag_card_tardy = u'迟到'
        if datetime.datetime.strptime(tag_card_time[1][1], "%Y-%m-%d %H:%M") <  stander_end_datetime :
            tag_card_early_leave = u'早退'
    else:
        tag_card_tardy = u'异常'
        tag_card_early_leave = u'异常'
    
    return (tag_card_tardy, tag_card_early_leave)


'''统计晚班的迟到早退数据 ==================================================
'''
def  static_gm_night(tag_card_sorted, seq_num):
    check_item = tag_card_sorted[seq_num]
    record_day = check_item[0]
    
    stander_start_time = record_day + " 22:00"
    stander_start_datetime = datetime.datetime.strptime(stander_start_time, "%Y-%m-%d %H:%M")
    stander_end_datetime = stander_start_datetime + datetime.timedelta(hours = 11)

    suppose_night_datetime = datetime.datetime.strptime(record_day + " 21:00", "%Y-%m-%d %H:%M")
    suppose_night_next_datetime = stander_end_datetime + datetime.timedelta(hours = -2)  # 预计早上7点就打卡走人
    tag_card_status = u'正常'
    tag_card_tardy = u'正常'
    tag_card_early = u'正常'
    
    if len(check_item[1]) == 2 :        
        start_datetime = datetime.datetime.strptime(check_item[1][1], "%Y-%m-%d %H:%M")        
        if start_datetime > suppose_night_datetime:
            if seq_num + 1 >= len(tag_card_sorted) :
                tag_card_status = u'未统计'
                tag_card_tardy = u'未统计'
                tag_card_early = u'未统计'
                delta_seconds = 0
            else:
                end_time = tag_card_sorted[seq_num + 1][1][0]
                if end_time == '':
                    tag_card_status = u'异常'
                    tag_card_tardy = u'异常'
                    tag_card_early = u'异常'
                    delta_seconds = 0
                else:
                    ''' 如果上下班的时间都有打卡，才需要分别表明 迟到 早退 的状态'''
                    end_datetime = datetime.datetime.strptime(end_time, "%Y-%m-%d %H:%M")
                    delta_seconds = (end_datetime - start_datetime).seconds
                    tag_card_status = u'正常'
                    if start_datetime >  stander_start_datetime :
                        tag_card_tardy = u'异常'
                    if end_datetime < stander_end_datetime :
                        tag_card_early = u'异常'
                        
    elif len(check_item[1]) == 1 :
        the_only_datetime = datetime.datetime.strptime(check_item[1][0], "%Y-%m-%d %H:%M")
        if the_only_datetime > suppose_night_datetime:
            if seq_num + 1 >= len(tag_card_sorted) :
                tag_card_status = u'未统计'
                tag_card_tardy = u'未统计'
                tag_card_early = u'未统计'
                delta_seconds = 0
            elif seq_num + 1 < len(tag_card_sorted):
                
                end_time = tag_card_sorted[seq_num + 1][1][0]
                if end_time == '':
                    ''' 第二天早上忘记打卡 '''
                    tag_card_status = u'异常'
                    tag_card_tardy = u'异常'
                    tag_card_early = u'异常'
                    delta_seconds = 0
                elif  ((datetime.datetime.strptime(end_time, "%Y-%m-%d %H:%M") - stander_end_datetime).seconds)/3600 > 10 :
                    ''' 第二天早上忘记打卡 '''                   
                    tag_card_status = u'异常'
                    tag_card_tardy = u'异常'
                    tag_card_early = u'异常'
                    delta_seconds = 0
                else:
                    end_datetime = datetime.datetime.strptime(end_time, "%Y-%m-%d %H:%M")
                    tag_card_status = u'正常'
                    delta_seconds = (end_datetime - the_only_datetime).seconds
                    if the_only_datetime > stander_start_datetime :
                        tag_card_tardy = u'迟到'
                    if end_datetime < stander_end_datetime :
                        tag_card_early = u'早退'
                                    
        elif ((suppose_night_datetime - the_only_datetime).seconds)/3600 > 9:
            '''晚上就上班就忘记打卡'''
            tag_card_status = u'异常'
            tag_card_tardy = u'异常'
            tag_card_early = u'异常'
            delta_seconds = 0
    
    elif check_item[1] == '':
        tag_card_status = u'异常'
        tag_card_tardy = u'异常'
        tag_card_early = u'异常'
        delta_seconds = 0
    
    return (tag_card_status, tag_card_tardy, tag_card_early, delta_seconds )
    
 

def save_to_xls(gm_static_work_duration_dict):
    cur_dir = r"E:\python_dev\gm_static" 
    now = datetime.datetime.now()
    
    file_name =  u"客服工作时长统计_" + now.strftime("%m_%d_%H_%M_%S")
    book = xlwt.Workbook(encoding='utf-8', style_compression=0)
    table = book.add_sheet(file_name, cell_overwrite_ok=True)
#    head = [u"姓名", u"日期", u'班次', u'时长/小时', u'星期', u'打卡' ]
    head = [u"姓名", u"日期", u'班次', u'时长/小时', u'星期', u'打卡', u'迟到', u'早退' ]
    '''写表头'''
    for col in range(len(head)):
        table.write(0, col, head[col])
    
    
    nrows = 1
    
    for gm_name, work_duration_dict in gm_static_work_duration_dict.items():
        temp_list = sorted(work_duration_dict.items(), key = lambda item: item[0])
        for item in temp_list :
            a_list_to_write = [gm_name, item[0]]
            a_list_to_write.extend(item[1])
            for col in range(len(head)):
                table.write(nrows, col, a_list_to_write[col])
            nrows = nrows + 1
        
    xl_name =cur_dir + "\\" +file_name  +".xls" 
    book.save(xl_name)


'''
     "__main__" 入口 =====================================================
'''
if __name__ == "__main__":
    '''
       获取统计表格的基本信息
    '''
    cur_dir = r"E:\python_dev\gm_static"  
    gm_plan_month_xls      = cur_dir + r"\gm_sort_month.xlsx"
    gm_record_xls          = cur_dir + r"\gm_record.xlsx"
#    print("当前所在路径： ",     cur_dir)
#    print("排班表文件：   ",    gm_plan_month_xls)
#    print("打卡记录文件：  ",    gm_record_xls)
    
    '''获取GM 的排班计划数据 ================================================'''
    wb_gm_plan_month, table_gm_plan_month = open_one_xls(gm_plan_month_xls)
    gm_work_plan_dict = {}
    ''' 
        gm_work_plan_dict  -- 字典，数据结构：
        {  name1: {'2017-08-01': '早',....}, name2 : {'2017-08-01': '休',......}, ..., nameN : {'2017-08-01': '夜',} }
    '''
    gm_work_plan_dict =  fetch_work_plan_data(wb_gm_plan_month, table_gm_plan_month)
    
    
    '''获取GM 的打卡记录数据 ================================================'''
    wb_gm_record, table_gm_record = open_one_xls(gm_record_xls)
    gm_record_dict = {}    
    ''' 
        gm_record_dict  -- 字典，GM 从OA导出的打卡记录，数据结果为：
        { name_1 : { '2017-08-07': ['2017-08-07 13:16', '2017-08-07 22:04'], ...}, 
          ..., 
          name_N : {'2017-08-13': ['2017-08-13 09:00', '2017-08-13 21:45'], ...} }
         GM 的名字是字典的key, 每个GM 的打卡记录又是一个小字典，以日期为key, 把当天的打卡记录以列表存储。
         注意：列表有可能是空的，因为：1. GM 忘记打卡； 2. 休假或请假       
    '''
    gm_record_dict = fetch_record_data(wb_gm_record, table_gm_record)
    
            
    '''计算GM 每天的工作时长 =============================================='''
    gm_static_work_duration_dict = static_work_duration_time(gm_work_plan_dict, gm_record_dict)
    
    save_to_xls(gm_static_work_duration_dict)
  